{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "180e8d82",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d32d21ea",
   "metadata": {},
   "source": [
    "### 读取Excel表格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6a96f8b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = pd.read_excel(\"上半年销售统计表.xlsx\", \n",
    "                    sheet_name=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da76ab7b",
   "metadata": {},
   "source": [
    "### 给Sheet添加月份列，并且合并多个表格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5bf88f6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = []\n",
    "for sheet_name, df in dfs.items():\n",
    "    df[\"月份\"] = sheet_name\n",
    "    df_list.append(df)\n",
    "df_all = pd.concat(df_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a9604a3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>书名</th>\n",
       "      <th>销量（册）</th>\n",
       "      <th>月份</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>基于数据分析的网店运营</td>\n",
       "      <td>20</td>\n",
       "      <td>3月</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>零基础轻松学Python青少年趣味编程</td>\n",
       "      <td>78</td>\n",
       "      <td>6月</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>数据透视表从入门到精通</td>\n",
       "      <td>99</td>\n",
       "      <td>2月</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    书名  销量（册）   月份\n",
       "1          基于数据分析的网店运营      20  3月\n",
       "6  零基础轻松学Python青少年趣味编程      78  6月\n",
       "6          数据透视表从入门到精通      99  2月"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be445482",
   "metadata": {},
   "source": [
    "### 透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "560809cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pivot = pd.pivot_table(df_all, \n",
    "                          index=\"书名\", \n",
    "                          columns=\"月份\",  \n",
    "                          values=\"销量（册） \",\n",
    "                          aggfunc=\"sum\",\n",
    "                          fill_value=0,\n",
    "                          margins=True,\n",
    "                          margins_name=\"汇总\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b319a90d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>月份</th>\n",
       "      <th>1月</th>\n",
       "      <th>2月</th>\n",
       "      <th>3月</th>\n",
       "      <th>4月</th>\n",
       "      <th>5月</th>\n",
       "      <th>6月</th>\n",
       "      <th>汇总</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>书名</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>128个图表教你搞定会计与财务管理</th>\n",
       "      <td>89</td>\n",
       "      <td>65</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Excel 2016实战技巧大全666招</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>145</td>\n",
       "      <td>69</td>\n",
       "      <td>45</td>\n",
       "      <td>45</td>\n",
       "      <td>304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Excel数据分析与决策</th>\n",
       "      <td>124</td>\n",
       "      <td>23</td>\n",
       "      <td>68</td>\n",
       "      <td>78</td>\n",
       "      <td>89</td>\n",
       "      <td>17</td>\n",
       "      <td>399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Excel财务与会计办公技巧应用大全</th>\n",
       "      <td>26</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Power BI智能数据分析与可视化从入门到精通</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>240</td>\n",
       "      <td>152</td>\n",
       "      <td>39</td>\n",
       "      <td>431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>一样的数据，不一样的看法</th>\n",
       "      <td>28</td>\n",
       "      <td>70</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>会计和财务一招致胜</th>\n",
       "      <td>78</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>商业智能：Power BI数据分析</th>\n",
       "      <td>0</td>\n",
       "      <td>55</td>\n",
       "      <td>87</td>\n",
       "      <td>25</td>\n",
       "      <td>236</td>\n",
       "      <td>360</td>\n",
       "      <td>763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>基于数据分析的网店运营</th>\n",
       "      <td>60</td>\n",
       "      <td>89</td>\n",
       "      <td>20</td>\n",
       "      <td>96</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>数据透视表从入门到精通</th>\n",
       "      <td>0</td>\n",
       "      <td>99</td>\n",
       "      <td>39</td>\n",
       "      <td>36</td>\n",
       "      <td>201</td>\n",
       "      <td>29</td>\n",
       "      <td>404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>跨境电商数据分析</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>100</td>\n",
       "      <td>87</td>\n",
       "      <td>16</td>\n",
       "      <td>251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>零基础轻松学C++青少年趣味编程</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>245</td>\n",
       "      <td>245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>零基础轻松学Python青少年趣味编程</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>345</td>\n",
       "      <td>78</td>\n",
       "      <td>423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>汇总</th>\n",
       "      <td>405</td>\n",
       "      <td>524</td>\n",
       "      <td>513</td>\n",
       "      <td>644</td>\n",
       "      <td>1155</td>\n",
       "      <td>829</td>\n",
       "      <td>4070</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "月份                         1月   2月   3月   4月    5月   6月    汇总\n",
       "书名                                                           \n",
       "128个图表教你搞定会计与财务管理          89   65   78    0     0    0   232\n",
       "Excel 2016实战技巧大全666招        0    0  145   69    45   45   304\n",
       "Excel数据分析与决策              124   23   68   78    89   17   399\n",
       "Excel财务与会计办公技巧应用大全         26   78    0    0     0    0   104\n",
       "Power BI智能数据分析与可视化从入门到精通    0    0    0  240   152   39   431\n",
       "一样的数据，不一样的看法               28   70   28    0     0    0   126\n",
       "会计和财务一招致胜                  78   45    0    0     0    0   123\n",
       "商业智能：Power BI数据分析           0   55   87   25   236  360   763\n",
       "基于数据分析的网店运营                60   89   20   96     0    0   265\n",
       "数据透视表从入门到精通                 0   99   39   36   201   29   404\n",
       "跨境电商数据分析                    0    0   48  100    87   16   251\n",
       "零基础轻松学C++青少年趣味编程            0    0    0    0     0  245   245\n",
       "零基础轻松学Python青少年趣味编程         0    0    0    0   345   78   423\n",
       "汇总                        405  524  513  644  1155  829  4070"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "215cd775",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pivot.to_excel(\"汇总透视表.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1cb81bc",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
